<?php
/*
 * @Author: CYH
 * @Date: 2020-10-31 10:25:06
 * @LastEditTime: 2021-04-22 10:37:43
 * @LastEditors: Please set LastEditors
 * @Description: 入住\退宿\调宿model类
 * @FilePath: \application\models\Register\Register_manage.php
 */
    // 入住\退宿\调宿model类
    class Register_manage extends CI_Model
    {
        //构造方法
        public function __construct()
        {

            parent::__construct();
            $this->load->database();
        }
        //入住查询方法
        // public function check_in_select($date_start, $date_end, $type, $RY_name, $RY_degsonid, $limit)
        // {
        //     //$type:1.入住2.调宿3.退宿
        //     $limit1 = ("$limit" - 1) * 10;
        //     $sj1 = $date_start == '' ? ' 1 = 1 ' : " SQ_live_date >= '" . $date_start . "'";
        //     $sj2 = $date_end == '' ? ' AND 1 = 1 ' : " AND SQ_live_date <= '" . $date_end . "'";
        //     $sj3 = $RY_degsonid == '' ? ' AND 1 = 1 ' : " AND SQ_RY_number LIKE '%" . $RY_degsonid . "%'";
        //     $sj4 = $RY_name == '' ? ' AND 1 = 1 ' : " AND SQ_RY_name LIKE '%" . $RY_name . "%'";
        //     $sj5 = $type == '' ? ' AND 1 = 1 ' : " AND SQ_type LIKE '%" . $type . "%'";
        //     $sj6 = $limit == '' ? ' AND 1 = 1 ' : "Limit " . $limit1 . ",10";
        //     $sql = 'SELECT * FROM dorm_stay_retreat_tune WHERE' . $sj1 . $sj2 . $sj3 . $sj4 . $sj5 . $sj6;
        //     $query = $this->db->query($sql, $sj1, $sj2, $sj3, $sj4, $sj5, $sj6);
        //     if ($query->num_rows() > 0) {
        //         $return_su = array('res' => '40000', 'msg' => '查询成功', 'data' => $query->result());
        //         $result = json_encode($return_su);
        //     } else {
        //         $return_fa = array('res' => '40001', 'msg' => '');
        //         $result = json_encode($return_fa);
        //     }
        //     return $result;
        // }

        //退宿查询方法
        // public function retreat_manage_select($date_start, $date_end, $type, $RY_name, $RY_degsonid, $limit)
        // {
        //     $limit1 = ("$limit" - 1) * 10;
        //     $sj1 = $date_start == '' ? ' 1 = 1 ' : " SQ_retreat_date BETWEEN '".$date_start."' AND '".$date_end."'";
        //     $sj2 = $RY_degsonid == '' ? ' AND 1 = 1 ' : " AND SQ_RY_number LIKE '%" . $RY_degsonid . "%'";
        //     $sj3 = $RY_name == '' ? ' AND 1 = 1 ' : " AND SQ_RY_name LIKE '%" . $RY_name . "%'";
        //     $sj4 = $type == '' ? ' AND 1 = 1 ' : " AND SQ_type LIKE '%" . $type . "%'";
        //     $sj5 = $limit == '' ? ' AND 1 = 1 ' : "Limit " . $limit1 . ",10";
        //     $sql = 'SELECT * FROM dorm_stay_retreat_tune WHERE' . $sj1 . $sj2 . $sj3 . $sj4 . $sj5;
        //     $query = $this->db->query($sql, $sj1, $sj2, $sj3, $sj4, $sj5);
        //     if ($query->num_rows() > 0) {
        //         $return_su = array('res' => '40000', 'msg' => '查询成功', 'data' => $query->result());
        //         $result = json_encode($return_su);
        //     } else {
        //         $return_fa = array('res' => '40001', 'msg' => '');
        //         $result = json_encode($return_fa);
        //     }
        //     return $result;
        // }

        /**
         * @description: 退宿/住宿/调宿查询方法_总条数
         * @param {date_start, date_end, type, name, jobNumber}
         * @return {*}
         */        
        public function dorm_manage_select_all($date_start, $date_end, $type, $name, $jobNumber)
        {
            //退宿
            if($type === "0")
            {
            $sj1 = $date_start == '' ? ' 1 = 1 ' : " retreatDate BETWEEN '".$date_start."' AND '".$date_end."'";
            $sj2 = $jobNumber == '' ? ' AND 1 = 1 ' : " AND jobNumber LIKE '%".$jobNumber."%'";
            $sj3 = $name == '' ? ' AND 1 = 1 ' : " AND name LIKE '%".$name."%'";
            $sj4 = "AND type = '0'";
            $sql = "SELECT * FROM dorm_stay_retreat_tune  WHERE" .$sj1 .$sj2 .$sj3 .$sj4;
            $query = $this->db->query($sql, $sj1, $sj2, $sj3, $sj4);
            $count = $query->num_rows();
            return $count;
            //入住
            }
            elseif($type === "1") 
            {
                $sj1 = $date_start == '' ? ' 1 = 1 ' : " liveDate BETWEEN '".$date_start."' AND '".$date_end."'";
                $sj2 = $jobNumber == '' ? ' AND 1 = 1 ' : " AND jobNumber LIKE '%".$jobNumber."%'";
                $sj3 = $name == '' ? ' AND 1 = 1 ' : " AND name LIKE '%".$name."%'";
                $sj4 = "AND type = '1'";
                $sql = "SELECT * FROM dorm_stay_retreat_tune WHERE" .$sj1 .$sj2 .$sj3 .$sj4;
                $query = $this->db->query($sql, $sj1, $sj2, $sj3, $sj4);
                $count = $query->num_rows();
                return $count;
            //调宿
            }
            elseif($type === "2") 
            {
                $sj1 = $date_start == '' ? ' 1 = 1 ' : " changeDate BETWEEN '".$date_start."' AND '".$date_end."'";
                $sj2 = $jobNumber == '' ? ' AND 1 = 1 ' : " AND jobNumber LIKE '%".$jobNumber."%'";
                $sj3 = $name == '' ? ' AND 1 = 1 ' : " AND name LIKE '%".$name."%'";
                $sj4 = "AND type = '2'";
                // $sql = 'SELECT * FROM dorm_livepeople WHERE' . $sj1 . $sj2 . $sj3 . $sj4;
                $sql = "SELECT * FROM dorm_stay_retreat_tune WHERE" .$sj1 .$sj2 .$sj3 .$sj4;
                $query = $this->db->query($sql, $sj1, $sj2, $sj3, $sj4);
                $count = $query->num_rows();
                return $count;
                
            } 
            elseif($type === "") 
            {
                $sj1 = $date_start == '' ? ' 1 = 1 ' : " apply_date BETWEEN '".$date_start."' AND '".$date_end."'";
                $sj2 = $jobNumber == '' ? ' AND 1 = 1 ' : " AND jobNumber LIKE '%".$jobNumber."%'";
                $sj3 = $name == '' ? ' AND 1 = 1 ' : " AND name LIKE '%".$name."%'";
                // $sj4 = "AND RY_live_check = 'X' OR RY_quit_check = 'X' OR RY_tune_in_check = 'X'";
                // $sql = 'SELECT * FROM dorm_livepeople WHERE' . $sj1 . $sj2 . $sj3 . $sj4;
                $sql = "SELECT * FROM dorm_stay_retreat_tune WHERE" .$sj1 .$sj2 .$sj3;
                $query = $this->db->query($sql, $sj1, $sj2, $sj3);
                $count = $query->num_rows();
                return $count;

            }
        }

        /**
         * @description: 退宿/住宿/调宿 查询方法
         * @param {date_start, date_end, type, name, jobNumber, limit, count}
         * @return {*}
         */        
        public function dorm_manage_select($date_start, $date_end, $type, $name, $jobNumber, $limit ,$count)
        {
            //退宿
            if($type === "0")
            {
                if($limit != '')
                {
                    $limit1 = ("$limit" - 1) * 10;
                }
                $sj1 = $date_start == '' ? ' 1 = 1 ' : " retreatDate BETWEEN '".$date_start."' AND '".$date_end."'";
                $sj2 = $jobNumber == '' ? ' AND 1 = 1 ' : " AND jobNumber LIKE '%".$jobNumber."%'";
                $sj3 = $name == '' ? ' AND 1 = 1 ' : " AND name LIKE '%" . $name . "%'";
                // $sj4 = $type == '' ? ' AND 1 = 1 ' : " AND RY_retreat_type LIKE '%" . $type . "%'";
                // $sj4 = $type == '' ? ' AND 1 = 1 ' : " AND SQ_type LIKE '%" . $type . "%'";
                // $sj5 = " AND RY_quit_check = 1 ";
                $sj4 = "AND type = '0'";
                $sj6 = $limit == '' ? ' AND 1 = 1 ' : " Limit " . $limit1 . ",10";
                // $sql = 'SELECT * FROM dorm_livepeople WHERE' . $sj1 . $sj2 . $sj3 . $sj4 . $sj6;
                if($limit != '')
                {
                    $sql = "SELECT * FROM dorm_stay_retreat_tune  WHERE" .$sj1 .$sj2 .$sj3 .$sj4 .$sj6;
                    $query = $this->db->query($sql, $sj1, $sj2, $sj3, $sj4, $sj6);
                } 
                else 
                {
                    $sql = "SELECT * FROM dorm_stay_retreat_tune  WHERE" .$sj1 .$sj2 .$sj3 .$sj4;
                    $query = $this->db->query($sql, $sj1, $sj2, $sj3, $sj4);
                }
            //入住
            }
            elseif($type === "1")
            {
                if($limit != '')
                {
                    $limit1 = ("$limit" - 1) * 10;
                }
                $sj1 = $date_start == '' ? ' 1 = 1 ' : " liveDate BETWEEN '".$date_start."' AND '".$date_end."'";
                $sj2 = $jobNumber == '' ? ' AND 1 = 1 ' : " AND jobNumber LIKE '%".$jobNumber."%'";
                $sj3 = $name == '' ? ' AND 1 = 1 ' : " AND name LIKE '%".$name."%'";
                // $sj4 = $type == '' ? ' AND 1 = 1 ' : " AND RY_retreat_type LIKE '%" . $type . "%'";
                // $sj4 = $type == '' ? ' AND 1 = 1 ' : " AND SQ_type LIKE '%" . $type . "%'";
                // $sj5 = " AND RY_quit_check = 1 ";
                $sj4 = "AND type = '1'";
                $sj6 = $limit == '' ? ' AND 1 = 1 ' : " Limit " . $limit1 .",10 ";
                // $sql = 'SELECT * FROM dorm_livepeople WHERE' . $sj1 . $sj2 . $sj3 . $sj4 . $sj6;
                if($limit != '')
                {
                    $sql = "SELECT * FROM dorm_stay_retreat_tune  WHERE" .$sj1 .$sj2 .$sj3 .$sj4 .$sj6;
                    $query = $this->db->query($sql, $sj1, $sj2, $sj3, $sj4, $sj6);
                } 
                else 
                {
                    $sql = "SELECT * FROM dorm_stay_retreat_tune  WHERE" .$sj1 .$sj2 .$sj3 .$sj4;
                    $query = $this->db->query($sql, $sj1, $sj2, $sj3, $sj4);
                }
            //调宿
            }
            elseif($type === "2")
            {
                if($limit != '')
                {
                    $limit1 = ("$limit" - 1) * 10;
                }
                $sj1 = $date_start == '' ? ' 1 = 1 ' : " changeDate BETWEEN '".$date_start."' AND '".$date_end."'";
                $sj2 = $jobNumber == '' ? ' AND 1 = 1 ' : " AND jobNumber LIKE '%".$jobNumber."%'";
                $sj3 = $name == '' ? ' AND 1 = 1 ' : " AND name LIKE '%".$name."%'";
                // $sj4 = $type == '' ? ' AND 1 = 1 ' : " AND RY_retreat_type LIKE '%" . $type . "%'";
                // $sj4 = $type == '' ? ' AND 1 = 1 ' : " AND SQ_type LIKE '%" . $type . "%'";
                // $sj5 = " AND RY_quit_check = 1 ";
                $sj4 = "AND type = '2'";
                $sj6 = $limit == '' ? ' AND 1 = 1 ' : " Limit " . $limit1 .",10 ";
                // $sql = 'SELECT * FROM dorm_livepeople WHERE' . $sj1 . $sj2 . $sj3 . $sj4 . $sj6;
                if($limit != '')
                {
                    $sql = "SELECT * FROM dorm_stay_retreat_tune  WHERE" .$sj1 .$sj2 .$sj3. $sj4 .$sj6;
                    $query = $this->db->query($sql, $sj1, $sj2, $sj3, $sj6);
                }
                else 
                {
                    $sql = 'SELECT * FROM dorm_stay_retreat_tune WHERE' .$sj1 .$sj2 .$sj3 .$sj4;
                    $query = $this->db->query($sql, $sj1, $sj2, $sj3);
                }
            //全查
            }
            elseif($type === "")
            {   
                if($limit != '')
                {
                    $limit1 = ("$limit" - 1) * 10;
                }
                $sj1 = $date_start == '' ? ' 1 = 1 ' : " apply_date BETWEEN '".$date_start."' AND '".$date_end."'";
                $sj2 = $jobNumber == '' ? ' AND 1 = 1 ' : " AND jobNumber LIKE '%".$jobNumber."%'";
                $sj3 = $name == '' ? ' AND 1 = 1 ' : " AND name LIKE '%".$name."%'";
                // $sj4 = $type == '' ? ' AND 1 = 1 ' : " AND RY_retreat_type LIKE '%" . $type . "%'";
                // $sj4 = $type == '' ? ' AND 1 = 1 ' : " AND SQ_type LIKE '%" . $type . "%'";
                // $sj5 = " AND RY_quit_check = 1 ";
                // $sj4 = "AND liveCheck = 'X'";
                $sj6 = $limit == '' ? ' AND 1 = 1 ' : " Limit " . $limit1 .",10 ";
                // $sql = 'SELECT * FROM dorm_livepeople WHERE' . $sj1 . $sj2 . $sj3 . $sj4 . $sj6;
                if($limit != '')
                {
                    // $sql = "SELECT * FROM dorm_livepeople  WHERE" .$sj1 .$sj2 .$sj3 .$sj4 .$sj6;
                    $sql = "SELECT * FROM dorm_stay_retreat_tune  WHERE" .$sj1 .$sj2 .$sj3 .$sj6;
                    $query = $this->db->query($sql, $sj1, $sj2, $sj3, $sj4, $sj6);
                } 
                else 
                {
                    $sql = "SELECT * FROM dorm_stay_retreat_tune  WHERE" .$sj1 .$sj2 .$sj3;
                    $query = $this->db->query($sql, $sj1, $sj2, $sj3);
                }

            }

            if ($query->num_rows() > 0) 
            {
                $return_su = array('res' => '40000', 'msg' => '查询成功', 'data' => $query->result() ,'count' => $count);
                $result = json_encode($return_su);
            } 
            else 
            {
                $return_fa = array('res' => '40001', 'msg' => '查询无数据' ,'data' => '');
                $result = json_encode($return_fa);
            }
            return $result;
        }

        //调宿查询方法
        // public function tune_in_select($date_start, $date_end, $type, $RY_name, $RY_degsonid, $limit){
        //     $limit1 = ("$limit" - 1) * 10;
        //     $sj1 = $date_start == '' ? ' 1 = 1 ' : " SQ_BG_date >= '" . $date_start . "'";
        //     $sj2 = $date_end == '' ? ' AND 1 = 1 ' : " AND SQ_BG_date <= '" . $date_end . "'";
        //     $sj3 = $RY_degsonid == '' ? ' AND 1 = 1 ' : " AND SQ_RY_number LIKE '%" . $RY_degsonid . "%'";
        //     $sj4 = $RY_name == '' ? ' AND 1 = 1 ' : " AND SQ_RY_name LIKE '%" . $RY_name . "%'";
        //     $sj5 = $type == '' ? ' AND 1 = 1 ' : " AND SQ_type LIKE '%" . $type . "%'";
        //     $sj6 = $limit == '' ? ' AND 1 = 1 ' : "Limit " . $limit1 . ",10";
        //     $sql = 'SELECT * FROM dorm_stay_retreat_tune WHERE' . $sj1 . $sj2 . $sj3 . $sj4 . $sj5 . $sj6;
        //     $query = $this->db->query($sql, $sj1, $sj2, $sj3, $sj4, $sj5, $sj6);
        //     if ($query->num_rows() > 0) {
        //         $return_su = array('res' => '40000', 'msg' => '查询成功', 'data' => $query->result());
        //         $result = json_encode($return_su);
        //     } else {
        //         $return_fa = array('res' => '40001', 'msg' => '查询失败');
        //         $result = json_encode($return_fa);
        //     }
        //     return $result;
        // }
    
        // public function excel_livepeople()
        // {
        //     $query = $this->db->query('SELECT B.area_name ,B.floor_name ,A.name ,A.jobNumber ,A.sex ,A.department ,A.job, A.room_number ,A.bedNumber, A.liveCheck ,A.liveDate ,A.quitCheck ,A.retreatDate ,A.tune_inCheck, A.tune_inDate FROM dorm_livepeople AS A
        //                                LEFT JOIN dorm_sslmanage AS B ON A.area_number = B.area_number AND A.floor_number = B.floor_number');
        //     $result = $query->result();
        //     return json_encode(array('res' => '40001', 'msg' => $result));
        // }
        
        /**
         * @description: 入住/退宿/调速数据导出
         * @param {*}
         * @return {*}
         */    
        public function excel_record()
        {
            $query = $this->db->query('SELECT A.area_number, A.floor_number, A.room_number, A.bedNumber, A.name, A.jobNumber, A.sex, A.department, A.job, A.liveDate, A.retreatDate, A.changeDate, A.type, B.area_name, B.floor_name FROM dorm_stay_retreat_tune AS A
                                        LEFT JOIN dorm_sslmanage AS B ON A.area_number = B.area_number AND A.floor_number = B.floor_number');
            $result = $query->result();
            return json_encode(array('res' => '40001', 'msg' => $result));
        }

        /**
         * @description: 入住/退宿/调速数据导出
         * @param {*}
         * @return {*}
         */
        public function excel_recording()
        {
            $query = $this->db->query('SELECT B.area_name ,B.floor_name ,A.name ,A.jobNumber ,A.sex ,A.department ,A.job, A.room_number ,A.bedNumber, A.liveCheck ,A.liveDate ,A.quitCheck ,A.retreatDate ,A.tune_inCheck, A.tune_inDate FROM dorm_livepeople AS A
                                       LEFT JOIN dorm_sslmanage AS B ON A.area_number = B.area_number AND A.floor_number = B.floor_number');
            $result = $query->result();
            return json_encode(array('res' => '40001', 'msg' => $result));
        }
}
